********************************************************************************
* Prepare exchange rates: bilateral and per USD
* 		euro transition
* 		merge with country - sector names
* 		generate ROW and Srest
********************************************************************************
clear
set more off

*global rootfolder ""
global inputfolder "$rootfolder\data"
global CSfolder "$rootfolder\analysis\CS"

capture mkdir "$rootfolder\analysis\FX"
global outputfolder "$rootfolder\analysis\FX"

capture mkdir "$outputfolder\MatlabFiles"
global matlabfolder "$outputfolder\MatlabFiles"

********************************************************************************
global YEAR_START 1995
global YEAR_END 2011
********************************************************************************

********************************************************************************
* FX data
********************************************************************************
import excel using "$inputfolder\2_FX\FX_USD.xlsx", clear sheet("FX_Rates") first
* data for EST LVA TWN added (BIS exchange rates, monthly average) [berik,20.12.2016]
set more off
gen date = mofd(A)
gen year = yofd(A)
gen month = month(A)
drop A

drop if year < 1994
drop if year > 2013

replace EUR = "." if EUR == "" | EUR == "NV"


destring EUR, replace force
destring RUS, replace force
destring BGR, replace force
tsset date, m
gen USA = 1

replace EUR = . if date<ym(1999,1)
order date

********************************************************************************
* Euro transition
********************************************************************************
* official currency-euro conversion rates
replace AUT = AUT / 13.7603 if date < ym(1999,1)
replace BEL = BEL / 40.3399 if date < ym(1999,1)
replace FIN = FIN / 5.94573 if date < ym(1999,1)
replace FRA = FRA / 6.55957 if date < ym(1999,1)
replace DEU = DEU / 1.95583 if date < ym(1999,1)
replace IRL = IRL / 0.787564 if date < ym(1999,1)
replace ITA = ITA / 1936.27 if date < ym(1999,1)
replace LUX = LUX / 40.3399 if date < ym(1999,1)
replace NLD = NLD / 2.20371 if date < ym(1999,1)
replace PRT = PRT / 200.482 if date < ym(1999,1)
replace ESP = ESP / 166.386 if date < ym(1999,1)

replace GRC = GRC / 340.75 if date < ym(2001,1)
replace SVN = SVN / 239.64 if date < ym(2007,1)
replace CYP = CYP / 0.585274 if date < ym(2007,1)
replace MLT = MLT / 0.4293 if date < ym(2007,1)
replace SVK = SVK / 30.126 if date < ym(2009,1)
replace EST = EST / 15.6466 if date < ym(2011,1)
replace LVA = LVA / 0.702804 if date < ym(2014,1)

* Euro after date of introduction
foreach c in AUT BEL FIN FRA DEU IRL ITA LUX NLD PRT ESP {
	replace `c' = EUR if date >= ym(1999,1)
}
replace GRC = EUR if date >= ym(2001,1)
replace SVN = EUR if date >= ym(2007,1)
replace CYP = EUR if date >= ym(2008,1)
replace MLT = EUR if date >= ym(2008,1)
replace SVK = EUR if date >= ym(2009,1)
replace EST = EUR if date >= ym(2011,1)
replace LVA = EUR if date >= ym(2014,1)

save "$outputfolder\FX_rates_temp.dta", replace


********************************************************************************
* Bilateral FX rates
********************************************************************************
use "$outputfolder\FX_rates_temp.dta",clear // (country per USD)
local importednames "AUS AUT BEL BGR BRA CAN CHN CYP CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IDN IND IRL ITA JPN KOR LTU LUX LVA MEX MLT NLD POL PRT ROM RUS SVK SVN SWE TUR TWN USA EUR"

local newvars 

* Bilateral:
foreach nvar in `importednames' {
	foreach dvar in `importednames' {
				gen `nvar'_`dvar' = `nvar'/`dvar' 
				local newvars `newvars' `nvar'_`dvar'
	}
}

foreach var in `newvars' {
 *di "`var'"
	gen dFX_`var' = ln(`var') - ln(l1.`var')
}

foreach var in `newvars' {
	ipolate dFX_`var' date, gen(tmp_dFX_`var')
	replace dFX_`var' = tmp_dFX_`var'
	drop tmp_dFX_`var'
}

keep date year month dFX_* 
drop in 1

reshape long dFX_, i(date) j(CP) s
rename dFX_ dFX_bil

gen country = substr(CP,1,3)
gen partner = substr(CP,5,3)
order date year month country partner

foreach tt in country partner {
	rename `tt' wiod_name
	joinby wiod_name using "$CSfolder\tempallc.dta", unm(master)
	rename wiod_name `tt'
	rename c `tt'_c
	drop _merge
}
rename country_c c_I
rename partner_c c_E
drop CP date
save "$outputfolder\FX_rates_bil_monthly.dta", replace

********************************************************************************
* FX rates against USD: USD/XXX
********************************************************************************
use "$outputfolder\FX_rates_temp.dta",clear // (country per USD)
local importednames "AUS AUT BEL BGR BRA CAN CHN CYP CZE DEU DNK ESP EST FIN FRA GBR GRC HUN IDN IND IRL ITA JPN KOR LTU LUX LVA MEX MLT NLD POL PRT ROM RUS SVK SVN SWE TUR TWN USA EUR"
local newvars 

* USD per country
foreach nvar in `importednames' {
				gen USA_`nvar' = 1/`nvar'
				local newvars `newvars' USA_`nvar'
	
}

foreach var in `newvars' {
 *di "`var'"
	gen dFX_`var' = ln(`var') - ln(l1.`var')
}

foreach var in `newvars' {
	ipolate dFX_`var' date, gen(tmp_dFX_`var')
	replace dFX_`var' = tmp_dFX_`var'
	drop tmp_dFX_`var'
}

keep date year month dFX_* 
drop in 1

reshape long dFX_, i(date) j(CP) s
rename dFX_ dFX_usd

gen country = substr(CP,1,3) 
gen partner = substr(CP,5,3)
order date year month country partner

foreach tt in country partner {
	rename `tt' wiod_name
	joinby wiod_name using "$CSfolder\\tempallc.dta", unm(master)
	rename wiod_name `tt'
	rename c `tt'_c
	drop _merge
}
rename country_c c_I
rename partner_c c_E
drop CP date
save "$outputfolder\FX_rates_USD_monthly.dta", replace

********************************************************************************
* Generate ROW and Srest
********************************************************************************
* Final sector list: 17 
local Li_Sectors "15t16 17t18 19 20 21t22 23 24 25 26 27t28 29 30t33 34t35 36t37 atb c e"
local List_Srest "50 51 52 h 60 61 62 63 64 j 70 71t74 l m n o p f"
* Final country list: 30 + ROW
local Li_Ctrys "AUS	AUT BEL BGR CAN CHN CZE DEU DNK ESP FIN FRA GBR GRC HUN IRL ITA JPN KOR LTU MEX NLD POL PRT ROM RUS SVN SWE TWN USA"
local List_ROW "BRA CYP EST IDN IND LUX LVA MLT SVK TUR"

********************************************************************************
* Bilateral FX rates
********************************************************************************
use "$outputfolder\FX_rates_bil_monthly.dta", clear	
**create ROW for FX ************************************************************
gen tmp_ctry_row = 0
gen tmp_partner_row = 0
foreach b of local List_ROW { 
	replace tmp_ctry_row = 1 if country == "`b'"
	replace tmp_partner_row = 1 if partner == "`b'"
}
replace country = "ROW" if tmp_ctry_row == 1
replace partner = "ROW" if tmp_partner_row == 1
collapse (mean) dFX_bil, by(year month country partner)
save "$outputfolder\FX_rates_bil_monthly_trunc.dta", replace

di "`List_ROW'"
di "`Li_Ctrys'"
di "`List_Srest'"
di "`Li_Sectors'"

********************************************************************************
* Export to csv as a vector to be used in Matlab
********************************************************************************
use "$outputfolder\FX_rates_bil_monthly_trunc.dta", clear
drop if country == "EUR"
drop if partner == "EUR"

forvalues mo = 1/12 {
	forvalues yr = $YEAR_START/$YEAR_END {
		preserve 
		
		keep year month country partner dFX_bil
		keep if year == `yr' & month == `mo'
		
		outsheet using "$matlabfolder\FX_bil_`yr'`mo'.csv", comma replace
		restore		
	}
}

********************************************************************************
* FX rates against USD: USD/XXX
********************************************************************************
use "$outputfolder\FX_rates_USD_monthly.dta", clear	
** Create ROW for FX 
gen tmp_ctry_row = 0
gen tmp_partner_row = 0

* local List_ROW "BRA CYP EST IDN IND LUX LVA MLT SVK TUR" // drop when running the whole script

foreach b of local List_ROW { 
	replace tmp_ctry_row = 1 if country == "`b'"
	replace tmp_partner_row = 1 if partner == "`b'"
}
replace country = "ROW" if tmp_ctry_row == 1
replace partner = "ROW" if tmp_partner_row == 1
collapse (mean) dFX_usd, by(year month country partner)
save "$outputfolder\FX_rates_USD_monthly_trunc.dta", replace

di "`List_ROW'"
di "`Li_Ctrys'"
di "`List_Srest'"
di "`Li_Sectors'"

********************************************************************************
* Export to csv as a vector to be used in Matlab
********************************************************************************
use "$outputfolder\FX_rates_USD_monthly_trunc.dta", clear
drop if partner == "EUR"

forvalues mo = 1/12 {
	forvalues yr = $YEAR_START/$YEAR_END {
		preserve 
		
		keep year month country partner dFX_usd
		keep if year == `yr' & month == `mo'
		
		outsheet using "$matlabfolder\FX_`yr'`mo'.csv", comma replace
		restore		
	}
}

********************************************************************************
* FX RATES AGAINST USD - IN LEVELS
** [Save rates against USD IN LEVELS for the "PPI denominated in the same currency" exercise]
********************************************************************************
use "$outputfolder\FX_rates_temp.dta", clear
rename * i_*
rename i_date date
rename i_month month
rename i_year year
reshape long i_ , i(date) j(country) s
rename i FX_usd
drop if country == "EUR"
assert FX_usd !=.

rename country wiod_name
joinby wiod_name using "$CSfolder\tempallc.dta", unm(master)
rename wiod_name country
rename c c_I
drop _merge date
	
save "$outputfolder\FX_rates_USDlevel_monthly.dta",replace
